package cn.lxycx.dataterrace.controller;


import com.jfinal.kit.PropKit;
import cn.lxycx.dataterrace.interceptor.validates.StatementValidate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.jfinal.aop.Before;
import com.jfinal.core.ActionKey;
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

public class StatementController extends Controller{
	private Logger log = Logger.getLogger(StatementController.class);
	private String driver = PropKit.get("driver");

	
	@ActionKey("/statementList")
	public void statementList(){
		List<Record> grouplist = Db.find("select id,classname from data_statement_class where status=1 order by createtime");
		this.setAttr("grouplist", grouplist);
		renderJsp("statementList.jsp");
	}
	
	@ActionKey("/authList")
	public void authList(){
		Integer id = this.getParaToInt("id");
		if(id!=null){
			this.setAttr("id", id);
			renderJsp("authList.jsp");
		}else{
			renderHtml("无效的ID");
		}
	}
	
	
	
	
	
	/**
	 * 
	 * 根据分组查询sql名称和id
	 * @author 邢超
	 * 创建时间：2017年9月22日
	 *
	 */
	public void findList(){
		Integer page = this.getParaToInt("page");
		Integer limit = this.getParaToInt("limit");
		//List<Record> namelist = Db.find();
		String sql_oracle = "select t.*,(select classname from data_statement_class where id=t.classid) classname,(select dbms_lob.substr(wm_concat(aid),4000) aid  from data_statement_limit t where sid=t.id) limit2 from data_statement t  order by id ";
		String sql_mysql = "select t.*,(select classname from data_statement_class where id=t.classid) classname,(select  aid  from data_statement_limit t where sid=t.id) limit2 from data_statement t  order by id ";

		Page<Record> list = Db.paginate(page, limit, "select *","from ("+ (driver.indexOf("oracle")>0?sql_oracle:sql_mysql) +") ttt");
		
		this.setAttr("code", 0);
		this.setAttr("msg", "查询成功");
		this.setAttr("count", list.getTotalRow());
		this.setAttr("list", list.getList());
		renderJson();
	}
	
	public void findStatementById(){
		String id = this.getPara("id");
		Record re = Db.findFirst("select * from data_statement where id=?",id);
		this.setAttr("code", 0);
		this.setAttr("msg", "查询成功");
		this.setAttr("data", re);
		renderJson();
	}
	
	
	
	/**
	 * 保存操作
	 * @author XingChao
	 */
	@Before(StatementValidate.class)
	public void save() {
		Map<String,Object> param = this.getAttr("param");
		boolean flag = false;
		if(param.containsKey("id")) {
			//更新
			flag = Db.update("data_statement","id",new Record().setColumns(param));
		}else {
			//新增
			Record record = new Record().setColumns(param);
			if(driver.indexOf("oracle")>0){
				record.set("id", "data_statement_seq.nextval");
			}
			flag = Db.save("data_statement",record);
		}
		
		if(flag) {
			this.setAttr("res", 1);
			this.setAttr("msg", "保存成功");			
		}else {
			this.setAttr("res", -1);
			this.setAttr("msg", "保存失败");
		}
		
		renderJson();
	}
	
	
	//修改状态
	public void upstatus() {
		Integer id = this.getParaToInt("id");
		Integer status = this.getParaToInt("status");
		if(id!=null&&status!=null) {
			boolean b = Db.update("data_statement", "id", new Record().set("id", id).set("status", status));
			if(b) {
				this.setAttr("res", 1);
				this.setAttr("msg", "操作成功");
			}else {
				this.setAttr("res", -1);
				this.setAttr("msg", "操作失败");
			}
		}else {
			this.setAttr("res", -1);
			this.setAttr("msg", "缺少必要参数");
		}
		renderJson();
	}
	
	/**
	 * 查询指定报表的 所有人
	 * @author XingChao
	 */
	public void findAllAcc(){
		Integer page = this.getParaToInt("page");
		Integer size = this.getParaToInt("limit");
		Integer sid = this.getParaToInt("sid");
		String username = this.getPara("username");
		Record user = this.getSessionAttr("user");
		//List<Record> allAcc = Db.find("select * from data_statement_account where (role>? or id=?) and id not in(select aid from data_statement_limit where sid=?) ",user.get("role"),user.get("id"),sid);
		Page<Record> allAcc = null;
		if(StringUtils.isNotBlank(username)) {
			String sql = "select id,account,department,username from data_statement_account where (role>? or id=?) and username like '%'||?||'%' and id not in(select aid from data_statement_limit where sid=?) order by department,id";
			allAcc = Db.paginateByFullSql(page, size, "select count(1) from ("+sql+") ttt", sql, user.get("role"),user.get("id"),username,sid);
		}else {
			String sql = "select id,account,department,username from data_statement_account where (role>? or id=?) and id not in(select aid from data_statement_limit where sid=?) order by department,id";
			allAcc = Db.paginateByFullSql(page, size, "select count(1) from ("+sql+") ttt", sql, user.get("role"),user.get("id"),sid);
		}
		
		Record limit = Db.findFirst("select wheres,inserts from data_statement t where id=?",sid);
		this.setAttr("code", 0);
		this.setAttr("msg", "查询成功");
		this.setAttr("count", allAcc.getTotalRow());
		this.setAttr("list", allAcc.getList());
		this.setAttr("limit", limit);
		renderJson();
	}
	
	
	/**
	 * 查询账号权限
	 * @author XingChao
	 */
	public void findLimitAcc(){
		String id = this.getPara("id");
		List<Record> list = Db.find("select l.id,aid,a.account,a.username,l.limits,l.constraint_select,inserts,updates from data_statement_limit l left join data_statement_account a on l.aid=a.id left join data_statement s  on sid = s.id where sid=?",id);
		this.setAttr("code", 0);
		this.setAttr("msg", "查询成功");
		this.setAttr("list", list);
		renderJson();
	}
	
	
	
	/**
	 * 分配权限
	 * @author XingChao
	 */
	public void upLimit(){
		Integer id = this.getParaToInt("id");
		Integer sid = this.getParaToInt("sid");
		String aid = this.getPara("aid");
		String limits = this.getPara("limits");
		String constraint = this.getPara("constraint_select");
		if(StringUtils.isNotBlank(aid)){
			
			boolean flag = false;
			if(id==null){//保存
				String[] aids = aid.split(",");
				List<Record> relist = new ArrayList<Record>();
				if(driver.indexOf("oracle")>0){
					for (String str : aids) {
						relist.add(new Record().set("id", Db.queryBigDecimal("select data_statement_limit_seq.nextval from dual")).set("sid", sid)
								.set("aid", str).set("limits", limits).set("constraint_select", constraint));
					}
				}else{
					for (String str : aids) {
						relist.add(new Record().set("sid", sid).set("aid", str).set("limits", limits).set("constraint_select", constraint));
					}
				}

				
				try {
					
					Db.batchSave("data_statement_limit",relist,relist.size());
					flag = true;
				} catch (Exception e) {
					log.error("不可重复添加：",e);
				}
			}else{//更新
				flag = Db.update("data_statement_limit","id", new Record().set("id", id).set("sid", sid)
						.set("aid", aid).set("limits", limits).set("constraint_select", constraint));	
			}
			
			if(flag){
				this.setAttr("code", 0);
				this.setAttr("msg", "操作成功");
			}else{
				this.setAttr("code", -1);
				this.setAttr("msg", "操作失败");
			}
		}else{
			this.setAttr("code", -1);
			this.setAttr("msg", "请选择账号");
		}
		
		
		renderJson();

	}
	
	
	/**
	 * 
	 * 删除权限
	 * @author XingChao
	 */
	public void delLimit(){
		Integer id = this.getParaToInt("id");
		boolean b = Db.delete("data_statement_limit", new Record().set("id", id));
		if(b){
			this.setAttr("code", 0);
			this.setAttr("msg", "查询成功");
		}else{
			this.setAttr("code", -1);
			this.setAttr("msg", "查询失败");
		}
		renderJson();
	}
	
	
}
